-- =============================================
-- Stored procedure to get authority groups for a specified user.
--
-- Modifications
-- 10/10/2003  E.Tatsui   Created
-- =============================================

CREATE     PROCEDURE amsp_CMGetContentAuthority
    @InContactID numeric
AS
BEGIN
  DECLARE
    @SuperUserFlag bit,
    @SuperGroupID numeric
  
  SET @SuperUserFlag = 0
  -- First, find out if this user is a member of a super group.
  SELECT @SuperUserFlag = 1,
         @SuperGroupID = a.ContentAuthorityGroupID
    FROM Content_Authority_Group a, Content_Authority_Producer b
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND a.SuperGroupFlag = 'Y'

  -- If super user, they have access to all the groups.
  IF @SuperUserFlag = 1
    SELECT a.ContentAuthorityGroupID,
           b.ContactID,
           b.ContentEditorFlag,
           b.ContentApproverFlag,
           b.NavEditorFlag,
           b.NavCreatorFlag,
           b.LayoutFlag,
           b.EditorFlag,
           b.CustomPageFlag,
           b.ComponentScriptFlag,
           b.UploadFlag,
           b.DefaultOwnerFlag,
           b.HighestToolbarLevelInd,
           'Y' AS SuperGroupFlag
      FROM Content_Authority_Group a, Content_Authority_Producer b
     WHERE b.ContactID = @InContactID
       AND b.ContentAuthorityGroupID = @SuperGroupID
  ELSE
    SELECT b.*, 'N' AS SuperGroupFlag
      FROM Producer a LEFT OUTER JOIN Content_Authority_Producer b
        ON a.ContactID = b.ContactID
     WHERE a.ContactID = @InContactID

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetContentAuthority] TO [IMIS]
GO